#!/bin/bash

# Configure
host="disciplinas.dcc.ufba.br" # "localhost"   
user="caiosba"                 # "ufba"        
db="MATB09_caiosba"            # "matb09"      
pw="8FqWK/B4YLwXw"             # "ufba"        

# Destroy tables
mysql -u $user -h $host -D $db -p"$pw" -e "SET foreign_key_checks = 0;
DROP TABLE IF EXISTS companies;
DROP TABLE IF EXISTS branch_offices;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS jobs;
DROP TABLE IF EXISTS hour_bank;
DROP TABLE IF EXISTS hours;
DROP TABLE IF EXISTS links;
DROP TABLE IF EXISTS branch_office_has_department;
DROP TABLE IF EXISTS holidays;
DROP TABLE IF EXISTS offs;
DROP TABLE IF EXISTS roles;
DROP TABLE IF EXISTS departments;
DROP TABLE IF EXISTS tmp;
SET foreign_key_checks = 1;"

# Create tables
mysql -u $user -h $host -D $db -p"$pw" < create.sql

# Create procedures
mysql -u $user -h $host -D $db -p"$pw" < procedures.sql

# Add some data

# Create an employee
mysql -u $user -h $host -D $db -p"$pw" -e "
INSERT INTO roles                        (name)                            VALUES ('user');
INSERT INTO companies                    (name)                            VALUES ('UFBA');
INSERT INTO branch_offices               (company_id, city_name)           VALUES (1, 'Salvador');
INSERT INTO departments                  (name)                            VALUES ('DCC');
INSERT INTO branch_office_has_department (department_id, branch_office_id) VALUES (1, 1);
INSERT INTO jobs                         (name)                            VALUES ('Professor');
INSERT INTO employees 
  (name, login, password, hours, salary, genre, branch_office_id, job_id, department_id, role_id) VALUES 
  ('Daniela', 'daniela', '123456', 10, 10000, 'f', 1, 1, 1, 1);"

# Holidays
mysql -u $user -h $host -D $db -p"$pw" -e "INSERT INTO holidays (branch_office_id, holiday_date, reason) VALUES (1, '2010-04-02', 'Sexta-Feira Santa');"
mysql -u $user -h $host -D $db -p"$pw" -e "INSERT INTO holidays (branch_office_id, holiday_date, reason) VALUES (1, '2010-04-21', 'Tiradentes');"
mysql -u $user -h $host -D $db -p"$pw" -e "INSERT INTO holidays (branch_office_id, holiday_date, reason) VALUES (1, '2010-07-02', 'Independência da Bahia');"
mysql -u $user -h $host -D $db -p"$pw" -e "SELECT business_days('2010-04-01','2010-04-30') AS 'Dias Úteis em Abril'"
mysql -u $user -h $host -D $db -p"$pw" -e "CALL holiday_days(1,4,2010,@bhd); SELECT @bhd AS 'Feriados no Escritório';"
mysql -u $user -h $host -D $db -p"$pw" -e "CALL bo_business_days(1,4,2010,@bbd); SELECT @bbd AS 'Dias Úteis no Escritório';"

# Expected hours in april, excluding offs
mysql -u $user -h $host -D $db -p"$pw" -e "CALL employee_hours(1,4,2010,@eh); SELECT @eh AS 'Horas Previstas em Abril sem Descontar Horas Justificadas'"

# Offs
mysql -u $user -h $host -D $db -p"$pw" -e "INSERT INTO offs (employee_id, hours, off_date, notes) VALUES (1, 1.7, '2010-04-08', 'Médico');"
mysql -u $user -h $host -D $db -p"$pw" -e "INSERT INTO offs (employee_id, hours, off_date, notes) VALUES (1, 2.3, '2010-04-20', 'Participação em Congresso');"
mysql -u $user -h $host -D $db -p"$pw" -e "CALL off_hours(1,4,2010,@oh); SELECT @oh AS 'Horas Faltantes Justificadas';"

# Create triggers
mysql -u $user -h $host -D $db -p"$pw" < triggers.sql

# Employee worked 50h in april 2010
mysql -u $user -h $host -D $db -p"$pw" -e "DELETE FROM hours;
CALL worked_hours(1,4,2010,@x);
INSERT INTO hours (employee_id, ip, in_or_out, time) VALUES (1, '127.0.0.1', 'out', '2010-04-02 08:30:00');
INSERT INTO hours (employee_id, ip, in_or_out, time) VALUES (1, '127.0.0.1', 'out', '2010-04-02 12:50:00');
INSERT INTO hours (employee_id, ip, in_or_out, time) VALUES (1, '127.0.0.1', 'in', '2010-04-09 14:13:00');
INSERT INTO hours (employee_id, ip, in_or_out, time) VALUES (1, '127.0.0.1', 'out', '2010-04-09 22:53:00');
INSERT INTO hours (employee_id, ip, in_or_out, time) VALUES (1, '127.0.0.1', 'dentro', '2010-04-16 12:00:00');
INSERT INTO hours (employee_id, ip, in_or_out, time) VALUES (1, '127.0.0.1', 'out', '2010-04-16 22:00:00');
INSERT INTO hours (employee_id, ip, in_or_out, time) VALUES (1, '127.0.0.1', 'in', '2010-04-22 07:00:00');
INSERT INTO hours (employee_id, ip, in_or_out, time) VALUES (1, '127.0.0.1', 'out', '2010-04-22 14:00:00');
INSERT INTO hours (employee_id, ip, in_or_out, time) VALUES (1, '127.0.0.1', 'in', '2010-04-23 06:25:00');
INSERT INTO hours (employee_id, ip, in_or_out, time) VALUES (1, '127.0.0.1', 'out', '2010-04-23 22:55:00');
INSERT INTO hours (employee_id, ip, in_or_out, time) VALUES (1, '127.0.0.1', 'out', '2010-04-26 07:10:00');
INSERT INTO hours (employee_id, ip, in_or_out, time) VALUES (1, '127.0.0.1', 'out', '2010-04-26 11:40:00');
INSERT INTO hours (employee_id, ip, in_or_out, time) VALUES (1, '127.0.0.1', 'in', '2010-04-27 22:00:00');
INSERT INTO hours (employee_id, ip, in_or_out, time) VALUES (1, '127.0.0.1', 'out', '2010-04-28 1:00:00');
";

# How many hours did the employee work in  april?
mysql -u $user -h $host -D $db -p"$pw" -e "CALL employee_hours(1,4,2010,@eh); SELECT @eh AS 'Horas Previstas em Abril'"
mysql -u $user -h $host -D $db -p"$pw" -e "CALL worked_hours(1,4,2010,@wh); SELECT float2hour(@wh) AS 'Horas Trabalhadas em Abril';"

# Create views
mysql -u $user -h $host -D $db -p"$pw" < views.sql

# Who is our employee?
mysql -u $user -h $host -D $db -p"$pw" -e "SELECT * FROM employee_info WHERE id = 1"

# Bonus hours for our employee
mysql -u $user -h $host -D $db -p"$pw" -e "SELECT * FROM extra_hours WHERE id = 1"

# Holidays for the branch office this year
mysql -u $user -h $host -D $db -p"$pw" -e "SELECT * FROM year_holidays WHERE branch_office_id = 1 "
